package Export;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import Models.*;
import Interfaces.*;

public class CodeStorageContext {

	// Definice tabulek
	public static final String HodnoceniTable = "[Hodnoceni]";
	public static final String TagTable = "[Tag]";
	public static final String UserTable = "[User]";
	public static final String AplikaceTable = "[Aplikace]";

	static SimpleDateFormat simpleDateFormat = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	
	public static String GetSQLDateString(Date input){
		return simpleDateFormat.format(input);
	}
	
	public static String BytesToHexString(byte[] bytes) {
		final char[] hexArray = {'0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F'};
	    char[] hexChars = new char[bytes.length * 2];
	    int v;
	    for ( int j = 0; j < bytes.length; j++ ) {
	        v = bytes[j] & 0xFF;
	        hexChars[j * 2] = hexArray[v >>> 4];
	        hexChars[j * 2 + 1] = hexArray[v & 0x0F];
	    }
	    return new String(hexChars);
	}
	
	public static byte[] HexStringToByteArray(String s) {
        int len = s.length();
        byte[] data = new byte[len / 2];
        for (int i = 0; i < len; i += 2) {
            data[i / 2] = (byte) ((Character.digit(s.charAt(i), 16) << 4)
                    + Character.digit(s.charAt(i+1), 16));
        }
        return data;
	}	
	
	public static class CodeStorageFactory
	{
		// TODO: Odebrat vypisy
		
		// Nastaveni pripojeni
		static final String driver = "net.sourceforge.jtds.jdbc.Driver";
		static final String connectionString = "jdbc:jtds:sqlserver://localhost:1433/gja";
		static final String user = "gja_user";
		static final String password = "test";
		
		public static User LoadUserSimple(int IDUser){
			String select = User.selectUser + "[IDUser]=" + Integer.toString(IDUser);
			return loadUserSimple(select);
		}
		
		public static User LoadUserSimple(String login){
			String select = User.selectUser + "[Login]='" + login + "'" ;
			return loadUserSimple(select);
		}
		
		public static Tag LoadTagSimple(int IDTag){
			String select = Tag.selectTag + "[IDTag]=" + IDTag;
			return loadTagSimple(select);			
		}
		
		public static Aplikace LoadAplikaceSimple(int IDAplikace){
			String select = Aplikace.selectAplikace + "[IDAplikace]=" + IDAplikace;
			return loadAplikaceSimple(select);
		}
		
		public static List<Aplikace> LoadAplikaceTopHodList(){
			List<Aplikace> output = new LinkedList<Aplikace>();
			for(IDBItem item: loadAplikaceMulti(Aplikace.selectAplikaceTopHodList))
				output.add((Aplikace)item);
			return output;
		}

		public static List<Aplikace> LoadAplikaceTopStazList(){
			List<Aplikace> output = new LinkedList<Aplikace>();
			for(IDBItem item: loadAplikaceMulti(Aplikace.selectAplikaceTopStazList))
				output.add((Aplikace)item);
			return output;
		}
		
		public static List<Aplikace> LoadAplikaceNewList(){
			List<Aplikace> output = new LinkedList<Aplikace>();
			for(IDBItem item: loadAplikaceMulti(Aplikace.selectNewList))
				output.add((Aplikace)item);
			return output;
		}

		public static List<Aplikace> SearchAplikace(String nazev, String popis, String tag, Float hodnoceni){
			boolean isPrevioslySet = false;
			List<Aplikace> output = new LinkedList<Aplikace>();
			
			StringBuilder sb = new StringBuilder();
			
			if(tag != null && !tag.isEmpty())
				sb.append(Aplikace.selectAplikaceWhereList);
			else
				sb.append(Aplikace.selectAplikaceList);
			
			if(tag != null && !tag.isEmpty() || nazev != null && !nazev.isEmpty() || popis != null && !popis.isEmpty())
				sb.append(" WHERE");
				
			if(nazev != null && !nazev.isEmpty())
			{
				sb.append(" LOWER([Nazev]) LIKE '%");
				sb.append(nazev.toLowerCase());
				sb.append("%'");
				isPrevioslySet = true;
			}
			
			if(popis != null && !popis.isEmpty())
			{
				if(isPrevioslySet)
					sb.append(" AND");	
				sb.append(" LOWER([Popis]) LIKE '%");
				sb.append(popis.toLowerCase());
				sb.append("%'");
				isPrevioslySet = true;
			}			

			if(tag != null && !tag.isEmpty())
			{
				if(isPrevioslySet)
					sb.append(" AND");	
				sb.append(" LOWER([Hodnota]) LIKE '%");
				sb.append(tag.toLowerCase());
				sb.append("%'");
				isPrevioslySet = true;
			}			
			
			Aplikace app;
			for(IDBItem item: loadAplikaceMulti(sb.toString()))
			{
				app = (Aplikace)item;
				if(hodnoceni == null || app.getPrumerHodnoceni() >= hodnoceni)
					output.add(app);
			}
			
			return output;
		}
		
		public static Aplikace LoadAplikaceSimpleLight(int IDAplikace){
			String select = Aplikace.selectAplikaceLight + "[IDAplikace]=" + IDAplikace;
			Connection conn = GenerateConnection();
			if(conn != null){
				try{
					Statement statement = conn.createStatement();
					
					if(statement != null){
						ResultSet results =  statement.executeQuery(select);
						if(results.next()){
							Aplikace app = new Aplikace(results.getInt("IDAplikace"), results.getInt("IDUser"), results.getString("Nazev"), results.getString("Popis"),
							results.getString("FileName"), results.getDate("DatumVytvoreni"), results.getDate("DatumZmena"),  
							results.getFloat("AvgHodnota"), results.getInt("PocetStazeni"), null);
							return app;
						}
					}	
					
				}catch(Exception e){
					System.out.println(e.getMessage());
				}finally{
					try {
						conn.close();
					} catch (SQLException e) {System.out.println(e.getMessage());}
				}
			}
			return null;
		}
		
		public static byte[] LoadAplikaceIkon(int IDAplikace){ 
			String select = Aplikace.selectAplikaceIkona + "[IDAplikace]=" + IDAplikace;
			Connection conn = GenerateConnection();
			if(conn != null){
				try{
					Statement statement = conn.createStatement();
					
					if(statement != null){
						ResultSet results =  statement.executeQuery(select);
						if(results.next()){
							return results.getBytes("Ikona");
						}
					}	
					
				}catch(Exception e){
					System.out.println(e.getMessage());
				}finally{
					try {
						conn.close();
					} catch (SQLException e) {System.out.println(e.getMessage());}
				}
			}
			return null;
		} 

		public static byte[] LoadAplikaceObsah(int IDAplikace){ 
			String select = Aplikace.selectAplikaceObsah + "[IDAplikace]=" + IDAplikace;
			Connection conn = GenerateConnection();
			if(conn != null){
				try{
					Statement statement = conn.createStatement();
					
					if(statement != null){
						ResultSet results =  statement.executeQuery(select);
						if(results.next()){
							return results.getBytes("Obsah");
						}
					}	
					
				}catch(Exception e){
					System.out.println(e.getMessage());
				}finally{
					try {
						conn.close();
					} catch (SQLException e) {System.out.println(e.getMessage());}
				}
			}
			return null;
		} 
		
		public static User FetchUserAplikace(User user){
			String select = Aplikace.selectAplikace + "[IDUser]=" + user.getID();
			List<IDBItem> apps = loadAplikaceMulti(select);
			
			for(IDBItem app : apps){
				FetchAplikaceTag((Aplikace)app);
				app.setMasterObject(user);
			}
			
			user.setChilds(apps);
			return user;
		} 
		
		public static Aplikace FetchAplikaceTag(Aplikace aplikace){
			String select = Tag.selectTag + "[IDAplikace]=" + aplikace.getID();
			List<IDBItem> tags = loadTagMulti(select);
			
			for(IDBItem tag : tags)
				tag.setMasterObject(aplikace);
			
			aplikace.setChilds(tags);
			return aplikace;			
		}
		
		public static List<User> TotalFetch(){
			
			
			List<IDBItem> users = loadUserMulti(User.selectUserList);
			List<IDBItem> aplikace = loadAplikaceMulti(Aplikace.selectAplikaceList);
			List<IDBItem> tags = loadTagMulti(Tag.selectTagList);
			
			List<User> outputUsers = new LinkedList<User>();
			for(IDBItem user : users){
				
				List<IDBItem> uApps = new LinkedList<IDBItem>();
				for(IDBItem app: aplikace){ 
					if(app.getMasterObjectID() == user.getID()){
						Aplikace a = (Aplikace)app;
						
						// Nalezeni tagu
						List<IDBItem> aTags = new LinkedList<IDBItem>();
						for(IDBItem tag: tags){
							if(tag.getMasterObjectID() == app.getID()){
								tag.setMasterObject(a);
								aTags.add(tag);
							}
						}
						// Zmenseni kolekce tagu
						tags.removeAll(aTags);
						a.setChilds(aTags);
						a.setMasterObject(user);
						uApps.add(a);
					}
				}
				aplikace.removeAll(uApps);
				user.setChilds(uApps);
				outputUsers.add((User)user);
			}
			
			users.clear();
			return outputUsers;
		}
		
		public static boolean AddAplikaceHodnoceni(int idAplikace, String ip, float value){
			Connection conn = GenerateConnection();
			if(conn != null){
				try
				{
					Statement statement = conn.createStatement();
					
					if(statement != null)
					{
						ResultSet id = statement.executeQuery(Aplikace.GetHodnoceniSaveParams(idAplikace, ip, value));
						if(id.next())
							return true;
					}	
					
				}catch(Exception e){
					// System.out.println(e.getMessage());
				}finally{
					try {
						conn.close();
					} catch (SQLException e) { System.out.println(e.getMessage());}
				}
			}
			
			return false;
		}
		
		public static boolean addAplikaceStazeni(int idAplikace) {
			Connection conn = GenerateConnection();
			if(conn != null){
				try
				{
					Statement statement = conn.createStatement();
					
					if(statement != null)
					{
						ResultSet id = statement.executeQuery(Aplikace.getStazenoIncrement(idAplikace));
						if(id.next())
							return true;
					}	
					
				}catch(Exception e){
					System.out.println(e.getMessage());
				}finally{
					try {
						conn.close();
					} catch (SQLException e) { System.out.println(e.getMessage());}
				}
			}
			
			return false;
		}
		
		// Obecne metody
		public static boolean SaveItem(IDBItem item){
			Connection conn = GenerateConnection();
			if(conn != null){
				try
				{
					
					if(item instanceof Aplikace){
						Aplikace app = (Aplikace) item;
						PreparedStatement pstmt = (PreparedStatement) conn.prepareStatement(item.saveParams());
						pstmt.setBytes(1, app.getObsah());
						if(app.getIkona() != null)
							pstmt.setBytes(2, app.getIkona());
						
						ResultSet id = pstmt.executeQuery();
						if(id.next() && item.isNew())
							item.setID(id.getInt(1));
						
						return true;						
						
					}else{
						Statement statement = conn.createStatement();
						if(statement != null)
						{
							ResultSet id = statement.executeQuery(item.saveParams());
							if(id.next() && item.isNew())
								item.setID(id.getInt(1));
							
							return true;
						}	
					}
					
				}catch(Exception e){
					System.out.println(e.getMessage());
				}finally{
					try {
						conn.close();
					} catch (SQLException e) { System.out.println(e.getMessage());}
				}
			}
			
			return false;
		}
		
		public static boolean DeleteITem(IDBItem item){
			Connection conn = GenerateConnection();
			if(conn != null){
				try
				{
					Statement statement = conn.createStatement();
					
					if(statement != null)
					{
						ResultSet rs = statement.executeQuery(item.deleteParams()); 
						if(rs.next() && rs.getInt(1) > 0)
							return true;
					}	
					
				}catch(Exception e){
					System.out.println(e.getMessage());
				}finally{
					try {
						conn.close();
					} catch (SQLException e) {System.out.println(e.getMessage());}
				}
			}
			return false;
		}
		
		// Interni metody
		static Connection GenerateConnection()
		{
			try{
			Class.forName(driver);
			Connection conn = DriverManager.getConnection(connectionString, user, password);
			return conn;
			}catch(Exception ex)
			{
				return null;
			}
		}
		
		static User loadUserSimple(String select)
		{
			Connection conn = GenerateConnection();
			if(conn != null){
				try
				{
					Statement statement = conn.createStatement();
					
					if(statement != null)
					{
						ResultSet results =  statement.executeQuery(select);
						if(results.next())
						{
							User user = new User(results.getInt("IDUser"), results.getString("Email"), results.getString("Login"), results.getString("Password"), results.getBoolean("IsAdmin"));
							return user;
						}
					}	
					
				}catch(Exception e){
					System.out.println(e.getMessage());
				}finally{
					try {
						conn.close();
					} catch (SQLException e) {System.out.println(e.getMessage());}
				}
				
			}
				
			return null;			
		}
		
		static List<IDBItem> loadUserMulti(String select){
			Connection conn = GenerateConnection();
			if(conn != null){
				
				LinkedList<IDBItem> output = new LinkedList<IDBItem>();
				try{
					Statement statement = conn.createStatement();
					
					if(statement != null){
						User user;
						ResultSet results =  statement.executeQuery(select);
						while(results.next()){
							user = new User(results.getInt("IDUser"), results.getString("Email"), results.getString("Login"), results.getString("Password"), results.getBoolean("IsAdmin"));
							output.add(user);
						}
					}	
					
				}catch(Exception e){
					System.out.println(e.getMessage());
				}finally{
					try {
						conn.close();
					} catch (SQLException e) {System.out.println(e.getMessage());}
				}
				return output;
			}
				
			return null;
		}
		
		static Aplikace loadAplikaceSimple(String select){
			Connection conn = GenerateConnection();
			if(conn != null){
				try
				{
					Statement statement = conn.createStatement();
					
					if(statement != null)
					{
						ResultSet results =  statement.executeQuery(select);
						if(results.next())
						{
							Aplikace app = new Aplikace(results.getInt("IDAplikace"), results.getInt("IDUser"), results.getString("Nazev"), results.getString("Popis"),
									results.getString("FileName"), results.getDate("DatumVytvoreni"), results.getDate("DatumZmena"),  
									results.getBytes("Obsah"), results.getBytes("Ikona"), results.getFloat("AvgHodnota"), results.getInt("PocetStazeni"), null);
							return app;
						}
					}	
					
				}catch(Exception e){
					System.out.println(e.getMessage());
				}finally{
					try {
						conn.close();
					} catch (SQLException e) {System.out.println(e.getMessage());}
				}
				
			}
				
			return null;
		}
	
		static List<IDBItem> loadAplikaceMulti(String select){
			Connection conn = GenerateConnection();
			if(conn != null){
				
				LinkedList<IDBItem> output = new LinkedList<IDBItem>();
				try{
					Statement statement = conn.createStatement();
					
					if(statement != null){
						Aplikace app;
						ResultSet results =  statement.executeQuery(select);
						while(results.next()){
							app = new Aplikace(results.getInt("IDAplikace"), results.getInt("IDUser"), results.getString("Nazev"), results.getString("Popis"),
							results.getString("FileName"), results.getDate("DatumVytvoreni"), results.getDate("DatumZmena"),  
							results.getFloat("AvgHodnota"), results.getInt("PocetStazeni"), null);
							output.add(app);
						}
					}	
					
				}catch(Exception e){
					System.out.println(e.getMessage());
				}finally{
					try {
						conn.close();
					} catch (SQLException e) {System.out.println(e.getMessage());}
				}
				return output;
			}
				
			return null;
		}
	
		static Tag loadTagSimple(String select){
			Connection conn = GenerateConnection();
			if(conn != null){
				try
				{
					Statement statement = conn.createStatement();
					
					if(statement != null)
					{
						ResultSet results =  statement.executeQuery(select);
						if(results.next())
						{
							Tag tag = new Tag(results.getInt("IDTag"), results.getInt("IDAplikace"), TagType.values()[results.getByte("Typ")], results.getString("Hodnota"));
							return tag;
						}
					}	
					
				}catch(Exception e){
					System.out.println(e.getMessage());
				}finally{
					try {
						conn.close();
					} catch (SQLException e) {System.out.println(e.getMessage());}
				}
				
			}
				
			return null;	
		}
	
		static List<IDBItem> loadTagMulti(String select){
			Connection conn = GenerateConnection();
			if(conn != null){
				
				LinkedList<IDBItem> output = new LinkedList<IDBItem>();
				try{
					Statement statement = conn.createStatement();
					
					if(statement != null){
						Tag tag;
						ResultSet results =  statement.executeQuery(select);
						while(results.next()){
							tag = new Tag(results.getInt("IDTag"), results.getInt("IDAplikace"), TagType.values()[results.getByte("Typ")], results.getString("Hodnota"));
							output.add(tag);
						}
					}	
					
				}catch(Exception e){
					System.out.println(e.getMessage());
				}finally{
					try {
						conn.close();
					} catch (SQLException e) {System.out.println(e.getMessage());}
				}
				return output;
			}
				
			return null;
		}	
	}
}
